![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Chapter 25
|
Parameter | Description |
---|---|
TIMED_STATISTICS | Setting TIMED_STATISTICS to TRUE enables SQL Trace and some of the dynamic performance tables to collect timed statistics such as CPU and elapsed times. Enabling timed statistics incurs significant overhead because most Oracle operations are now being timed; avoid this parameter except when necessary. |
MAX_DUMP_FILE_SIZE | Specifies the maximum size of trace file dumps in OS blocks. Set this fairly low to avoid filling up the file system with trace files. If the SQL Trace output files are being truncated, increase this value. |
USER_DUMP_DEST | This parameter specifies the destination for the trace file. The default destination is the same as for system dumps on your OS. |
You can enable the SQL Trace facility on a per-session basis or for the entire instance. The following sections explain how to enable and disable SQL Trace for both of these cases.
Enable SQL Trace for a Session
To enable SQL Trace for a session, use this Oracle command:
ALTER SESSION SET SQL TRACE = TRUE;
Alternatively, you can use the Oracle procedure RDBMS_SESSION.SET_SQL_TRACE.
To enable SQL Trace for a session other than your own, you can use the Oracle procedure RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION with the arguments SID, Serial#, and TRUE. To determine the values for SID and Serial#, use the following SQL statements:
SQL> SELECT sid, serial#, osuser 2 FROM v$session 3 WHERE osuser = 'Ed Whalen'; SID SERIAL# OSUSER ----------- ------- ------------ 7 4 Ed Whalen
To turn SQL Trace on for that session, use the Oracle stored procedure as follows:
SQL> EXECUTE RDBMS_system.set_sql_trace_in_session(7,4,TRUE); PL/SQL procedure successfully completed.
Disable SQL Trace for a Session
To disable SQL Trace for a session, use this Oracle command:
ALTER SESSION SET SQL TRACE = FALSE;
The SQL Trace facility is also disabled when your session disconnects from Oracle.
To disable SQL Trace for a session other than your own, use the Oracle procedure RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION with the arguments SID, Serial#, and FALSE as shown here:
SQL> EXECUTE RDBMS_system.set_sql_trace_in_session(7,4,FALSE); PL/SQL procedure successfully completed.
Enable SQL Trace for an Instance
To enable SQL Trace for your instance, set the Oracle initialization parameter SQL_TRACE to TRUE. Doing so enables SQL Trace for all users of this instance for the duration of the instance.
Disable SQL Trace for an Instance
The SQL Trace facility cannot be disabled for the entire instance without shutting down the Oracle instance and setting the Oracle initialization parameter SQL_TRACE to FALSE. Alternatively, you can remove the parameter because its default value is FALSE.
When SQL Trace is enabled for the entire instance, it is still possible to disable it on a per-session basis. You can disable SQL Trace on a per-session basis with the SQL statement shown in the preceding section.
Previous | Table of Contents | Next |
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |